/*
 * This class is part of the book "iText in Action - 2nd Edition"
 * written by Bruno Lowagie (ISBN: 9781935182610)
 * For more info, go to: http://itextpdf.com/examples/
 * This example only works with the AGPL version of iText.
 */

package part1.chapter02;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.itextpdf.text.Anchor;
import com.itextpdf.text.Chunk;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.pdf.PdfWriter;
import com.lowagie.database.DatabaseConnection;
import com.lowagie.database.HsqldbConnection;
import com.lowagie.filmfestival.FilmFonts;
import com.lowagie.filmfestival.Movie;
import com.lowagie.filmfestival.PojoFactory;

public class MovieLinks1 {

	/** The resulting PDF file. */
	public static final String RESULT = MovieLinks1.class.getClassLoader().getResource("").getPath()
			+ "results/part1/chapter02/movie_links_1.pdf";

	/**
	 * Main method.
	 * 
	 * @param args
	 *          no arguments needed
	 * @throws DocumentException
	 * @throws IOException
	 * @throws SQLException
	 */
	public static void main(String[] args) throws IOException, DocumentException, SQLException {
		new MovieLinks1().createPdf(RESULT);
	}

	/**
	 * Creates a PDF document.
	 * 
	 * @param filename
	 *          the path to the new PDF document
	 * @throws DocumentException
	 * @throws IOException
	 * @throws SQLException
	 */
	public void createPdf(String filename) throws IOException, DocumentException, SQLException {
		// step 1
		Document document = new Document();
		// step 2
		PdfWriter.getInstance(document, new FileOutputStream(filename));
		// step 3
		document.open();
		// step 4
		// Create database connection and statement
		DatabaseConnection connection = new HsqldbConnection("filmfestival");
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery("SELECT DISTINCT mc.country_id, c.country, count(*) AS c "
				+ "FROM film_country c, film_movie_country mc " + "WHERE c.id = mc.country_id "
				+ "GROUP BY mc.country_id, country ORDER BY c DESC");
		Anchor imdb;
		// loop over the countries
		while (rs.next()) {
			Paragraph country = new Paragraph();
			// the name of the country will be a destination
			Anchor dest = new Anchor(rs.getString("country"), FilmFonts.BOLD);
			dest.setName(rs.getString("country_id"));
			country.add(dest);
			country.add(String.format(": %d movies", rs.getInt("c")));
			document.add(country);
			// loop over the movies
			for (Movie movie : PojoFactory.getMovies(connection, rs.getString("country_id"))) {
				// the movie title will be an external link
				imdb = new Anchor(movie.getMovieTitle());
				imdb.setReference(String.format("http://www.imdb.com/title/tt%s/", movie.getImdb()));
				document.add(imdb);
				document.add(Chunk.NEWLINE);
			}
			document.newPage();
		}
		// Create an internal link to the first page
		Anchor toUS = new Anchor("Go back to the first page.");
		toUS.setReference("#US");
		document.add(toUS);

		document.close();
		connection.close();
	}
}